import os, sys
from datetime import date

import xlrd
from sqlalchemy.orm import mapper
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

from model.config_parser import OpenHEAConfig
from model.schema import livelihoodzone

def transpose(grid):
    return zip(*grid)

def removeBlankRows(grid):
    return [list(row) for row in grid if any(row)]

def removeBlankRowsAndColumns(grid):
    return removeBlankRows(transpose(removeBlankRows(transpose(grid))))

def sheetToGrid(sheet):
    grid = []
    for rownum in range(sheet.nrows):
        grid.append(sheet.row_values(rownum))
    return grid

def sheetToGridNoBlank(sheet):
    return removeBlankRowsAndColumns(sheetToGrid(sheet))

class LivelihoodZone(object):

    def __init__(self, project, livelihoodzone, createdby=None,
                datecreated=None, consumptionyearbegins=None,
                population=None, coordinates=None):
        self.project = project
        self.livelihoodzone = livelihoodzone
        self.createdby = createdby
        self.datecreated = datecreated
        self.consumptionyearbegins = consumptionyearbegins
        self.population = population
        self.coordinates = coordinates

mapper(LivelihoodZone, livelihoodzone)

class DataImporter:
    def __init__(self, spreadsheet):
        # database session
        config_file = os.path.join(os.path.dirname(__file__), 'openhea.cfg')
        self.config = OpenHEAConfig()
        read = self.config.read(config_file)
        if len(read) != 1:
            print 'Need openhea.cfg setup with database parameters'
            sys.exit(1)
        cs = self.config.sqlalchemy_connection_string()
        engine = create_engine(cs, echo=True)
        Session = sessionmaker(bind=engine)
        self.session = Session()
        # open workbook
        self.workbook = xlrd.open_workbook(spreadsheet)

    def saveSiteData(self):
        site_data = sheetToGridNoBlank(self.workbook.sheet_by_index(0))
        project = site_data[1][1]
        livelihoodzone = site_data[2][1]
        date_tuple = xlrd.xldate_as_tuple(site_data[4][1], self.workbook.datemode)
        datecreated = date(date_tuple[0], date_tuple[1], date_tuple[2])
        createdby = site_data[5][1]
        consumptionyearbegins = site_data[7][1]
        lz = LivelihoodZone(project, livelihoodzone, createdby,
                datecreated, consumptionyearbegins)
        self.session.add(lz)
        self.session.commit()

def main(doc):
    di = DataImporter(doc)
    di.saveSiteData()

if __name__ == '__main__':
    sample_doc = os.path.join(os.path.dirname(__file__), '..', '..', 'EXAMPLEHEADATA.xls')
    sys.exit(main(sample_doc))

